Objetos de una BD

Tablas

Es la unidad básica de almacenamiento de datos. Los datos están almacenados en filas y columnas. Son de existencia permanente y poseen un nombre identificatorio. Cada columna tiene entre otros datos un nombre, un tipo de datos y un ancho (este puede estar predeterminado por el tipo de dato)

Motor SQL Server

CREATE TABLE ordenes (

N_orden int NULL ,

N_cliente int NULL ,

F_orden datetime NULL ,

C_estado smallint NULL ,

F_alta_audit timestamp NULL ,

D_usuario varchar (20) NULL )

Tablas temporales

Es posible crear tablas temporales que duran mientras se ejecute la aplicación o lo que el usuario desee dentro de la misma sesion. No es posible alterar tablas temporarias. Si Dropearlas y crear los índices que necesite la aplicación. Las tablas temporales pueden ser creadas y usadas mientras dure la sesión. La tabla no grabará ningún log transaccional si así se configura. No se actualizan las tablas de catálogo.

En algunos motores de BD las Tablas temporales generadas pueden ser Globales para las cuáles los datos en una tabla temporal serán automáticamente borrados en el caso de la terminación de la sesión. Cada sesión sólo puede ver y modificar sus propios datos. Sobre ellos, no se generan bloqueos de DML.

Motor SqlServer

Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles sólo en la sesión actual; las tablas temporales globales son visibles para todas las sesiones.

Coloque un prefijo de signo numérico simple (#table_name) en los nombres de las tablas temporales locales y un prefijo de un signo numérico doble (##table_name) en los nombres de las tablas temporales globales.

Creación de tabla temporal en base a la tabla ordenes. La tabla Ordenes_pendientes se borrará automáticamente cuando finalice la sesión.

CREATE TABLE #ordenes_pendientes (

N_orden INTEGER,

N_cliente INTEGER,

F_orden DATE,

I_Total DECIMAL(15 , 2),

C_estado SMALLINT,

F_alta_audit TIMESTAMP,

D_usuario VARCHAR(20) )

WITH NO LOG;

INSERT INTO #ordenes_Pendientes

SELECT * FROM ordenes WHERE c_estado = 1

Creación y carga de una tabla temporal desde un SELECT.

SELECT *

INTO #ordenes_Pendientes

FROM ordenes

WHERE c_estado = 1

Tablas anidadas (Oracle)

Es posible crear una tabla con una columna cuyo tipo de dato sea otra tabla. De esta forma, las tablas pueden anidarse dentro de otras tablas como valores en una columna.

Tablas Organizadas por Índice (IOT - Index-Organized Tables) (Oracle)

Una IOT tiene una organización del almacenamiento que es una variante del Arbol-B. A diferencia de una tabla común, en la que los datos se guardan como un conjunto desordenado, en una IOT se guardan en una estructura de índice de Árbol-B, ordenado a la manera de una clave primaria. Sin embargo, además de almacenar los valores de las columnas de clave primaria de cada tabla, cada entrada en el índice almacena además los valores de las columnas no clave.

De esta forma, en vez de mantener dos estructuras separadas de almacenamiento, una para la tabla y una para el índice de Árbol-B, el sistema mantiene sólo un índice Árbol-B, porque además de almacenar el rowid de las filas, también se guardan las columnas no clave.

Clusters (Agrupamientos) (Oracle)

Un cluster es un grupo de tablas que comparten los mismos bloques de datos porque tienen columnas comunes compartidas y que a menudo se usan juntas. Cuando se agrupan tablas, el motor guarda físicamente todas las filas de cada una de ambas tablas en los mismos bloques de datos.

Esto brinda algunos beneficios:

• Reducción de E/S a disco para los joins de las tablas agrupadas
• Mejora en tiempos de acceso para los joins de las tablas agrupadas
• En un cluster, el valor de clave de cluster es el valor de las columnas clave del cluster para una fila en particular. Cada valor de clave del cluster se guarda sólo una vez en el cluster y en el índice del cluster, independientemente de cuántas filas de diferentes tablas contengan dicho valor. Por lo tanto, se requiere menos espacio para almacenar tablas relacionadas e índices de datos en un cluster.

Constraints (Restricciones)

Integridad de entidades:

Es usada para asegurar que los datos pertenecientes a una misma tabla tienen una única manera de identificarse, es decir que cada fila de cada tabla tenga una primary key capaz de identificar unívocamente una fila y esa no puede ser nula.

Interviene:

• PRIMARY KEY CONSTRAINT: Puede estar compuesta por una o más columnas, y deberá representar unívocamente a cada fila de la tabla. No debe permitir valores nulos.

Integridad Referencial:

Es usada para asegurar la coherencia entre los datos. Ej.: Si se ingresa un comprobante de un cliente, este debe existir en la tabla de clientes.

Intervienen:

• PRIMARY KEY CONSTRAINT
• FOREIGN KEY CONSTRAINT: Puede estar compuesta por una o más columnas, y estará referenciando a la PRIMARY KEY de otra tabla.

Los constraints referenciales permiten a los usuarios especificar claves primarias y foráneas para asegurar una relación PADRE-HIJO (MAESTRO-DETALLE).

Reglas que son aseguradas con el uso de constraints referenciales:

1. Si un usuario BORRA (DELETE) una PRIMARY KEY y dicha clave está correspondida por FOREIGNS KEY en otras tablas, el DETELE fallará. Esta regla podría pasarse mediante la aplicación de cláusulas de ON DELETE CASCADE (Borrado en Cascada).
2. Si un usuario MODIFICA (UPDATE) una PRIMARY KEY y la clave original está correspondida por FOREIGNS KEY en otras tablas, el UPDATE fallará
3. No hay restricciones asociadas al borrado de FOREIGNS KEYS.
4. Si un usuario MODIFICA (UPDATE) una FOREIGN KEY y no hay una PRIMARY KEY en la tabla de referencia que corresponda a esa nueva clave NO NULA, el UPDATE fallará.
5. Todos los valores en una PRIMARY KEY deben ser únicos. Al tratar de insertar una clave duplicada en una PRIMARY KEY dará un error.
6. Cuando un usuario INSERTA (INSERT) una fila en una tabla hijo, si todas las FOREIGN KEYS son NO NULAS, todas deberán estar correspondidas por una PRIMARY KEY, en caso contrario, el INSERT fallará.

TIPOS DE CONSTRAINTS REFERENCIALES

1. CICLIC REFERENTIAL CONTSTRAINT: Asegura una relación de PADRE-HIJO entre tablas. Es el más común. Ej. CLIENTE -> FACTURAS PK Clientes.c_cliente FK Facturas.c_cliente que referencia a la PK de la tabla Clientes.
2. SELF REFERENCING CONSTRAINT: Asegura una relación de PADRE-HIJO entre la misma tabla. Ej. EMPLEADOS -> EMPLEADOS

PK Empleados.c_empleado

FK Empleados.c_jefe que referencia a la PK de la tabla Empleados, este campo además admite NULOS. Esto significa que para todo empleado que el campo jefe sea distinto de NULL, dicho código debe existir como empleado.

Ej. CLIENTES -> FACTURAS

CLIENTES -> RECLAMOS

PK Clientes.c_cliente

FK Facturas.c_cliente que referencia a la PK de la tabla Clientes.

FK Reclamos.c_cliente que referencia a la PK de la tabla Clientes.

Integridad Semántica

Es la que nos asegura que los datos que vamos a almacenar tengan una apropiada configuración.

• DATA TYPE: Este define el tipo de valor que se puede almacenar en una columna.
• DEFAULT CONSTRAINT: Es el valor insertado en una columna cuando al insertar un registro ningún valor fue especificado para dicha columna. El valor default por default es el NULL.
• Se aplica a columnas no listadas en una sentencia INSERT.
• El valor por default puede ser un valor literal o una función SQL (USER, TODAY, etc.)
• Aplicado sólo durante un INSERT (NO UPDATE).
• CHECK CONSTRAINT: Especifica condiciones para la inserción o modificación en una columna. Cada fila insertada en una tabla debe cumplir con dichas condiciones. Actua tanto en el INSERT, como en el UPDATE.
• Es una expresión que devuelve un valor booleano de TRUE o FALSE.
• Son aplicados para cada fila que es INSERTADA o MODIFICADA.
• Todas las columnas a las que referencia deben ser de la misma tabla (la corriente).
• No puede contener subconsultas, secuencias, funciones (de fecha, usuario) ni pseudocolumnas.
• Todas las filas existentes en una tabla deben pasar un nuevo constraint creado para dicha tabla. En el caso de que alguna de las filas no cumpla, no se podrá crear dicho constraint.
• UNIQUE CONSTRAINT: Especifica sobre una o más columnas que la inserción o actualización de una fila contiene un valor único en esa columna o conjunto de columnas.
• NOT NULL CONSTRAINT: Asegura que una columna contenga un valor durante una operación de INSERT o UPDATE. Se considera el NULL como la ausencia de valor.

Motor SQL Server

Creación de una tabla con Primary Key y otra con una Primary key compuesta y con una Foreign key.

CREATE TABLE ordenes (

N_orden int PRIMARY KEY ,

N_cliente int NULL ,

F_orden datetime NULL ,

C_estado smallint NULL ,

F_alta_audit datetime NULL ,

D_usuario varchar (20) NULL )

CREATE TABLE items_ordenes (

N_orden int REFERENCES ordenes ,

N_item int NOT NULL ,

C_producto int NULL ,

Q_cantidad int NULL ,

I_precunit numeric(9, 3) NULL,

PRIMARY KEY (N_orden, N_item) )

Creación de una tabla con Primary Key y una Foreign Key consigo misma.

CREATE TABLE empleados (

N_empleado int PRIMARY KEY ,

D_Apellido varchar (60) NULL ,

D_nombres varchar (60) NULL ,

N_cuil numeric(11, 0) NULL ,

N_jefe int NULL REFERENCES empleados )

El motor no permitirá ingresar un empleado cuyo nro. de jefe no exista como número de empleado. Lo que si permitirá es ingresar un nro. de jefe NULO.

Creación de una tabla con una restricción de NOT NULL y otra de DEFAULT.

CREATE TABLE ordenes (

N_orden int NOT NULL ,

N_cliente int NULL ,

F_orden datetime NULL ,

C_estado smallint NULL DEFAULT 1,

F_alta_audit datetime NULL ,

D_usuario varchar (20) NULL )

El motor de BD no permitirá ingresar un registro a la tabla ordenes con un nro. de orden NULA y en sólo en el caso de un Insert si campo c_estado viene NULO le asignará por default el valor 1.

Creación de una tabla con una restricción de UNIQUE y otra de CHECK

CREATE TABLE empleados (

N_empleado int IDENTITY (1, 1) NOT NULL ,

D_Apellido varchar (60) NULL ,

D_nombres varchar (60) NULL ,

N_cuil numeric(11, 0) NULL UNIQUE,

F_nacimiento datetime NULL ,

F_ingreso datetime NULL ,

N_jefe int NULL,

CHECK (F_nacimiento < F_ingreso) )

Al ingresar un registro el sistema va a validar que no haya en la tabla de empleados otro empleado con el mismo nro. de cuil, y tanto para la inserción como para las modificaciones futuras se va a asegurar que para cada registro de la tabla siempre se cumpla el valor del campo f_ingreso sea mayor el valor del campo f_nacimiento.

Secuencias

Los generadores de secuencias proveen una serie de números secuenciales, especialmente usados en entornos multiusuarios para generar una números secuenciales y únicos sin el overhead de I/O a disco o el lockeo transaccional.

Una secuencia debe tener un nombre, debe ser ascendente o descendente, debe tener definido el intervalo entre números, tiene definidos métodos para obtener el próximo número ó el actual (entre otros).

Motor SQL Server

El motor SQL Server no posee secuencias, en su defecto se define un campo como IDENTITY que permite realizar lo mismo. Al insertar una fila en dicha tabla, el motor va a buscar el próximo nro. del más alto existente en la tabla.

CREATE TABLE ordenes (

N_orden int IDENTITY (1, 1) NOT NULL ,

N_cliente int NULL ,

F_orden datetime NULL ,

I_Total decimal(15 , 2),

C_estado smallint NULL ,

F_alta_audit datetime NULL ,

D_usuario varchar (20) NULL )

INSERT INTO ordenes

(N_cliente, F_orden, I_total, C_estado, F_alta_audit, D_usuario)

VALUES (17, '15/05/2006', 100, 1, GETDATE(), USER)

Las funciones GETDATE() y USER son funciones propias de sql Server y recuperan el año, mes, día, hora, minutos, segundos y fracción, y el usuario que tiene abierto la sesión, respectivamente. El valor para el campo identity no debe completarse.

Sinónimos ( NickName / Synonym )

Es un alias definido sobre una tabla, vista ó snapshot. Dependiendo el motor de BD puede ser también definido sobre un procedure, una secuencia, función o package. Se usan a menudo por seguridad o por conveniencia (por ej. en entornos distribuidos). Permiten enmascarar el nombre y dueño de un determinado objeto. Proveen de una ubicación transparente para objetos remotos en una BD distribuida. Simplifican las sentencias SQL para usuarios de la BD.

Database Links (Enlaces de Base de Datos) (Oracle)

Un database link es un puntero que define una ruta de comunicacion unidirecciónal desde un servidor de base de datos hasta otro. Para acceder al enlace, se debe estar conectado a la base de datos local que contiene la entrada en el diccionario de datos que define dicho puntero.

Hay dos tipos de enlace según la forma en que ocurre la conexión a la base remota:

• Enlace de usuario conectado: el usuario debe tener una cuenta en la base remota con el mismo nombre de usuario de la base local.
• Enlace de usuario fijo: el usuario se conecta usando el nombre de usuario y password referenciados en el enlace.

Directories (Directorios) (Oracle)

Un directorio especifica un alias para un directorio en el file system del servidor, donde se ubican archivos binario externos (BFILES) y datos de tablas externas.

Se pueden usar nombres de directorios al referirse a ellos desde el código, en vez de hardcodear el nombre de ruta de sistema operativo, suministrando por lo tanto una mayor flexibilidad en la administración de archivos.

Los directorios no son propiedad de ningún esquema individual.

Views (Vistas)

También llamada una tabla virtual o ventana dinámica, es un conjunto de columnas, ya sea reales o virtuales, de una misma tabla o no, con algún filtro determinado o no.

De esta forma, es una presentación adaptada de los datos contenidos en una o más tablas, o en otras vistas. Una vista toma la salida resultante de una consulta y la trata como una tabla. Se pueden usar vistas en la mayoría de las situaciones en las que se pueden usar tablas. A diferencia de una tabla, una vista no aloca espacio de almacenamiento, ni contiene datos almacenados, sino que está definida por una consulta que extrae u obtiene datos desde las tablas a las que la vista hace referencia.

Se pueden utilizar para:

1. Suministrar un nivel adicional de seguridad restringiendo el acceso a un conjunto predeterminado de filas o columnas de una tabla.
2. Ocultar la complejidad de los datos.
3. Simplificar sentencias al usuario.
4. Presentar los datos desde una perspectiva diferente a la de la tabla base.
5. Aislar a las aplicaciones de los cambios en la tabla base.

RESTRICCIONES:

1. No se pueden crear índices en las Views
2. Una view depende de las tablas a las que se haga referencia en ella, si se elimina una tabla todas las views que dependen de ella se borraran o se pasará a estado INVALIDO, dependiendo del motor. Lo mismo para el caso de borrar una view de la cual depende otra view.
3. Algunas views tienen restringido los: Inserts, Deletes, Updates.
• Aquellas que tengan joins
• Una función agregada
• Tener en cuenta ciertas restricciones para el caso de Actualizaciones:
• Si en la tabla existieran campos que no permiten nulos y en la view no aparecen, los inserts fallarían
• Si en la view no aparece la primary key los inserts podrían fallar
• No se pueden definir Triggers sobre una Vista.
• Se puede borrar filas desde una view que tenga una columna virtual.
• Con la opcion WITH CHECK OPTION, se puede actualizar siempre y cuando el checkeo de la opción en el where sea verdadero.
• Al crear la view el usuario debe tener permiso de select sobre las columnas de las tablas involucradas.
• No es posible adicionar a una View las cláusulas de: ORDER BY y UNION.

Motor SQL Server

Creación de Vista a partir de un Select de dos tablas.

CREATE VIEW V_Ordenes_Pendientes

AS

SELECT ordenes.N_orden AS Orden_nro, ordenes.F_orden AS Fecha_orden, clientes.d_apellido + ',' + clientes.d_nombre AS Cliente, ordenes.i_total AS Importe_total

FROM ordenes INNER JOIN clientes ON ordenes.N_cliente = clientes.n_cliente

WHERE (ordenes.C_estado = 1)

SELECT * FROM v_ordenes_pendientes

Obtendríamos por ejemplo los siguientes resultados:

Orden_nro

Fecha_orden Cliente

122

15/05/2006

Ledesma, Mario

123

15/05/2006

Contempomi, Felipe

....

Importe_total

1234.56

2345,78

Indices

Son estructuras opcionales asociadas a una tabla. La función de los índices es la de permitir un acceso más rápido a los datos de una tabla, se pueden crear distintos tipos de índices sobre uno o más campos. Los índices son lógica y físicamente independientes de los datos en la tabla asociada. Se puede crear o borrar un índice en cualquier momento sin afectar a las tablas base o a otros índices.

TIPOS DE INDICES

1. Btree Index : Estructura de índice estándar.
2. Btree Cluster Index : La tabla se ordena igual que el índice.
3. Reverse Key Index (Oracle) : Invierte los bytes de la clave a indexar. Esto sirve para los índices cuyas claves son una serie constante con por ej. Crecimiento ascendente. para que las inserciones se distribuyan por todas las hojas del árbol de índice.
4. Bitmap Index (Oracle) : Son utilizados para pocas claves con muchas repeticiones . Cada bit en el Bitmap corresponde a una fila en particular. Si el bit esta en on significa que la fila con el correspondiente rowid tiene el valor de la clave.

CARACTERÍSTICAS DIFERENCIADORAS PARA LOS ÍNDICES

1. Unique : Índice de clave única.
2. Cluster : Este tipo de índice provoca al momento de su creación que físicamente los datos de la tabla sean ordenados por el mismo. (Informix / SQLServer / DB2) . Variante en ORACLE: un Cluster es un objeto de la base que contiene los datos de una o más tablas, con una o más columnas en común. Oracle almacena juntas todas las filas (de todas las tablas) que comparten la misma clave de cluster. Un índice cluster, crea un índice en el cluster que se especifica.
3. Duplicado : Permite múltiples entradas para una misma clave.
4. Compuesto La clave se compone de varias columnas.

Las principales funciones de un índice compuesto son:

1. Facilitar múltiples joins entre columnas
2. Incrementar la unicidad del valor de los índices

Ejemplo de índice compuesto:

customer_nun, lname, fname

Este índice se usará en o para los siguientes casos:

• Joins sobre customer_num, o customer_num y lname o customer_num, lname y fname, es decir, sentencias donde la cláusula WHERE haga referencia a todo o a una porción inicial del índice (las columnas más selectivas o más accedidas, irán al principio del índice).
• Filtros sobre customer_num, o customer_num y lname o customer_num, lname y fname.
• ORDERS BY sobre o customer_num y lname o customer_num, lname y fname.
• Joins sobre customer_num y Filtros sobre lname y fname.
• Joins sobre customer_num y lname y Filtros sobre fname.
1. Function based Index (Oracle): Calcula el valor de la función o la expresión, y lo guarda en el índice. Puede crearse tanto como un índice B-Tree como Bitmap.

BENEFICIOS DE LA UTILIZACIÓN DE INDICES:

1. Se le provee al sistema mejor perfomance al equipo ya que no debe hacer lecturas secuenciales sino accede a través de los índices, sólo en los casos que las columnas del Select no formen parte del índice.
2. Mejor perfomance en el ordenamiento de filas
3. Asegura únicos valores para las filas almacenadas
4. Cuando las columnas que intervienen en un JOIN tienen índices se le da mejor perfomance si el sistema logra recuperar los datos a través de ellas

COSTO DE LA UTILIZACIÓN DE INDICES

1. El primer costo asociado es el espacio que ocupa en disco, que en algunos casos suele ser mayor al que ocupan los datos.
2. El segundo costo es el de procesamiento, hay que tener en cuenta que cada vez que una fila es insertada o modificada o borrada, el índice debe estar bloqueado, con lo cual el sistema deberá recorrer y actualizar los distintos índices.

¿CUANDO DEBERIAMOS INDEXAR ?

• Indexar columnas que intervienen en Joins
• Indexar las columnas donde se realizan filtros
• Indexar columnas que son frecuentemente usadas en orders by
• Evitar duplicación de índices : Sobre todo en columnas con pocos valores diferentes Ej: Sexo, Estado Civil, Etc.
• Limitar la cantidad de índices en tablas que son actualizadas frecuentemente : Porque sobre estas tablas se estarán ejecutando Selects extras
• Verificar que el tamaño de índice debería ser pequeño comparado con la fila : Tratar sobre todo en crear índices sobre columnas cuya longitud de atributo sea pequeña. No crear índices sobre tablas con poca cantidad de filas, no olvidar que siempre se recupera de a páginas. De esta manera evitaríamos que el sistema lea el árbol de índices
• Tratar de usar índices compuestos para incrementar los valores únicos : Tener en cuenta que si una o más columnas intervienen en un índice compuesto el optimizador podría decidir acceder a través de ese índice aunque sea solo para la búsqueda de los datos de una columna, esto se denomina “partial key search”
• Usando cluster index se agiliza la recuperación de filas : Uno de los principales objetivos de la Optimización de bases de datos es reducir la entrada/salida de disco. Reorganizando aquellas tablas que lo necesiten, se obtendría como resultado que las filas serían almacenadas en bloques contiguos, con lo cual facilitaría el acceso y reduciría la cantidad de accesos ya que recuperaría en menos páginas los mismos datos.

CONSTRUCCIÓN DE ÍNDICES EN PARALELO

Los motores de BD usan en general métodos de construcción de índices en paralelo. El arbol B+ es construido por 2 o más procesos paralelos. Para esto el motor realiza una muestra de la filas a Indexar (aproximadamente 1000) y luego decide como separar en grupos. Luego scanea las filas y las ordena usando el mecanismo de sort en paralelo. Las claves ordenadas son colocadas en los grupos apropiados para luego ir armarndo en paralelo un subárbol por cada grupo. Al finalizar los subárboles se unen en un único Arbol B+.

Motor SQL Server

• Sql Server utiliza una estructura de Arbol B+.
• Máxima cantidad de campos para la clave de un índice compuesto: 16.

Creación de un índice único y simple:

CREATE UNIQUE INDEX ix1_ordenes ON ordenes (n_orden);

Creación de Indice duplicado y compuesto.

CREATE INDEX ix2_ordenes ON ordenes (n_cliente, f_orden);

Creación de Indice cluster.

CREATE CLUSTERED INDEX ix3_ordenes ON ordenes(N_orden) ON [PRIMARY];

Manejo del Load Factor

FILLFACTOR– Porcentaje de cada página del índice a ser dejado como espacio libre en su creación. Por ej. Si el FILLFACTOR=20, en la creación del índice se ocupará hasta el 80% de cada nodo.

CREATE UNIQUE INDEX ix1_ordenes ON ordenes(N_orden)

WITH FILLFACTOR = 20;

Snapshots / Summary Table / Materialized Views

Los snapshots, tambièn llamados vistas materializadas o tablas sumarizadas, son objetos del esquema de una BD que pueden ser usados para sumarizar, precomputar, distribuir o replicar datos.

Se utilizan sobre todo en DataWarehouse, sistemas para soporte de toma de decisión, y para computación móvil y/o distribuida. Consumen espacio de almacenamiento en disco. Deben ser recalculadas o refrescadas cuando los datos de las tablas master cambian. Pueden ser refrescadas en forma manual, o a intervalos de tiempo definidos dependiendo el motor de BD.

Funciones Propias de Motor (Built in Functions)

Estos objetos son funciones ya desarrolladas con el Motor de BD, las cuales pueden clasificarse de la siguiente manera:

Funciones Agregadas

Se aplican a un conjunto de valores derivados de una expresión, actúan específicamente en agrupamientos. SUM, COUNT, AVG, MAX, MIN, etc.

Funciones Escalares

Se aplican a un dato específico de cada fila de una consulta, o en una comparación en la sección WHERE.

• Funciones Algebraicas and Trigonometricas
• Funciones Estadisticas
• Funciones de Fecha
• Funciones de Strings
• Otras

Funciones de Tablas (algunos motores)

Retornan el equivalente a una tabla y pueden ser usadas solamente en la sección FROM de una sentencia. ORACLE: Se denominan “Vista en línea (inline view)”

Funciones de Usuario

Una función es un objeto de la base de datos que puede recibir uno o más parámetros de input y devolver sólo un parámetro de output.

Motor SQL Server

CREATE FUNCTION nombre_dpto (@p_c_empleado INT) RETURNS varchar(30)

AS

BEGIN

RETURN (SELECT d_dpto FROM departamentos d, empleados e

WHERE d.c_dpto = e.c_dpto AND e.c_empleado = p_c_empleado)

END

Invocación de función en un Select en la sección WHERE

SELECT * FROM departamentos d WHERE d.d_dpto = nombre_dpto(6010)

Invocación de función en un Select en la sección Lista de Columnas

SELECT e.c_empleado, e.d_nombre, e.d_apellido, nombre_dpto(e.c_dpto) depto_nombre

FROM empleados e WHERE e.c_empleado = 6010

Stored Procedures

Es un procedimiento almacenado como un objeto en la Base de Datos.

Características:

1. Incluyen sentencias de SQL y sentencias de lenguaje propias. Lenguaje SPL (Informix), PL/SQL (Oracle), TRANSAC/SQL (SQL Server).
2. Son almacenados en la base de Datos
• Solo las sentencias del lenguaje pr son permitidas además de las de SQL
• Algunos motores permiten además Stored Procedures en JAVA.
• Se guarda la sentencia SQL ya parseada y optimizada
• Antes de ser almacenada en la base de datos las sentencias SQL son parseadas y optimizadas. Cuando el stored procedure es ejecutado puede que no sea necesario su optimización, en caso contrario se optimiza la sentencia antes de ejecutarse.

VENTAJAS DE LOS STORED PROCEDURES:

1. Pueden reducir la complejidad en la programación. Creando SP con las funciones + usadas.
2. Pueden ganar perfomance en algunos casos
3. Otorgan un nivel de seguridad extra
4. Pueden definirse ciertas reglas de negocio independientemente de las aplicaciones.
5. Diferentes aplicaciones acceden al mismo código ya compilado y optimizado.
6. En un ambiente cliente servidor, no sería necesario tener distribuido el código de la aplicación
7. En proyectos donde el código puede ser ejecutados desde diferentes interfaces, Ud. mantiene un solo tipo de código.
8. Menor tráfico en el PIPE / SOCKET, no en la cantidad de bytes que viajan sino en los ciclos que debo ejecutar una instrucción.

Ej.: Si este proceso se ejecuta desde una workstation en red, por la red viajarán 1000 instrucciones Update/Delete, x cada una el motor tendrá que Parsearla y Optimizarla. El costo es muy alto.

FOR

SELECT ....

IF status

UPDATE

ELSE

DELETE

END IF

END FOR

Sería mejor hacer desde la aplicación cliente servidor , ejecutar un stored procedure:

EXECUTE PROCEDURE PEPE(var,var) 1 sola vez

En este caso por la red viajará sólo 1 instrucción (execute procedure...), el motor no parseará las instrucciones porque ya están parseadas dentro del Procedure, y las optimizará en caso de que sea necesario.

Motor SQL Server

Creación de un Stored Procedure que inserta una orden de pedido a partir de los datos existentes en varias tablas termporales manejando una transacción y creación de un procedure de grabación de log de errores.

CREATE PROCEDURE dbo.sp_inserta_orden @v_n_orden INT

AS SET NOCOUNT ON

DECLARE @nError int, @error_info char(30)

SET @nError = 0

BEGIN TRANSACTION

INSERT INTO ordenes SELECT * FROM ordenes_tmp WHERE n_orden = @v_n_orden

INSERT INTO item_ordenes SELECT * FROM items_tmp WHERE n_orden = @v_n_orden

set @nError = @@error

IF( @nError <> 0 )

BEGIN

ROLLBACK

SELECT @nError AS nError, @error_info AS Descripcion

exec sp_error_log @nError, @error_info, @v_n_orden, 'sp_inserta_orden'

END

ELSE

BEGIN

COMMIT

SELECT 0 AS nError ,'Se insertó bien' AS Descripcion

END

GO

CREATE PROCEDURE dbo.sp_error_log @sql_err int, @error_info char(70), @v_nro_orden INT, @proc_name char(18)

AS SET NOCOUNT ON

INSERT INTO error_logs VALUES (@proc_name, @v_nro_orden, @sql_err, @error_info, USER, getdate())

GO

Ejecución del Stored Procedure desde SQL de forma Online

EXEC sp_inserta_oden 1234

Ejecutará el procedure insertando la orden Nro. 1234. En el ejemplo se asume que las ordenes a pasar como parámetro existen y fueron validadas por otro programa.

Triggers

Es un mecanismo que ejecuta una sentencia de SQL automáticamente cuando cierto evento ocurre.

Eventos:

1. Insert
2. Update
3. Delete

¿Que se puede ejecutar en el trigger?:

1. Insert
2. Update
3. Delete
4. Codigo Procedural
5. Ejecutar un SP

Cuando el evento ocurre sobre una tabla, se dispara la acción.

Esta ejecución es independiente de la aplicación que la invoca

Solo en el evento UPDATE puedo hacer referencia a una columna

Cuando un trigger es ejecutado:

¿PORQUE USAR TRIGGERS?

1. Se pueden aplicar las reglas de negocio : Por Ej.: Si el inventario de una columna pasa x valor, entonces insertar un pedido en la tabla de compras
2. Valores de columnas derivadas : En algunos casos es necesario que ante tal acción se deriven datos en base a otros.
3. Replicación automática de tablas
4. Logs. De Auditoría
5. Delete en Cascada
6. Autorización de Seguridad

Eventos:

INSERT ON tab_name

DELETE FROM tab_name

UPDATE tab_name

UPDATE of col_name ON tab_name

CONEXION (Oracle)

DESCONEXION (Oracle)

Observaciones:

• Se permiten múltiples triggers sobre una tabla, pero sólo 1 por tipo. Para el caso de UPDATE las columnas deben ser mutuamente exclusivas.
• La tabla especificada por el trigger debe estar en modo local, no acepta tablas en servers remotos.

Acciones de triggers

Pueden ser sentencias SQL, 1 o varias, o Stored Procedures. En caso de Oracle se puede ejecutar directamente código PL/SQL.

Se pueden ejecutar en distintos momentos:

• before (execute procedure xyz()) -> Se ejecuta antes de que el evento de trigger ocurra
• for each row (execute procedure xyz()) -> Se ejecuta para cada una de las filas del evento
• after (execute procedure xyz()) -> Se ejecuta despues de que el evento de trigger ocurra

USOS EN LOGIN Y RECOVERY

• Para BD sin logs No ocurre el Rollback.
• En base de datos con log, el evento y la acción del trigger se les hace un roll back automático.

Motor SQL Server

CREATE TRIGGER employee_insupd ON employee FOR INSERT, UPDATE

AS

/* Get the range of level for this job type from the jobs table. */

DECLARE @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallint

SELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id

FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_id

IF (@job_id = 1) and (@emp_lvl <> 10)

BEGIN

RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)

ROLLBACK TRANSACTION

END

ELSE IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)

BEGIN

RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1, @job_id, @min_lvl, @max_lvl)

ROLLBACK TRANSACTION

END

Packages (Oracle / DB2)

Un Package (paquete) es un objeto que agrupa tipos y subprogramas relacionads lógicamente. Habitualmente tienen dos partes: una especificación y un cuerpo. La especificación es la interfaz para las aplicaciones: declara los tipos, variables, constantes, excepciones, cursores y subprogramas disponibles para su uso. El cuerpo define en forma completa los cursores y subprogramas, y por lo tanto implementa la especificación.

Los paquetes suministran varias ventajas: modularidad, facilidad en el diseño de la aplicación, ocultamiento de información y mejora en el rendimiento.

Esquema (Oracle)

Es el conjunto de objetos de los que es dueño un usuario, y tiene el mismo nombre que el usuario. Cada usuario posee un solo esquema. El esquema lo crea Oracle al crearse el usuario.

DTS (SqlServer)

Los Servicios de transformación de datos (DTS) solo existen en el motor SQL Server y proporcionan un conjunto de herramientas que permiten extraer, transformar y consolidar datos de distintos orígenes (Tablas de base de datos, excel, Archivos de Texto, Access, etc) en uno o varios destinos compatibles con la conectividad DTS. Para crear soluciones de transferencia de datos personalizadas se deben generar gráficamente los paquetes.

Importación y exportación de datos.

DTS puede importar datos de un archivo de texto o de un origen de datos OLE DB (por ejemplo, una base de datos de Microsoft Access 2000) en SQL Server. De forma alternativa, puede exportar datos desde SQL Server a un destino de datos OLE DB (por ejemplo, una hoja de cálculo Microsoft Excel 2000). DTS también permite la carga de datos de alta velocidad desde archivos de texto a tablas de SQL Server.

Transformación de datos.

El Diseñador DTS incluye la tarea Transformar datos, que permite seleccionar datos de una conexión de origen de datos, asignar las columnas de datos a un conjunto de transformaciones y enviar los datos transformados a una conexión de destino. El Diseñador DTS también contiene una tarea de consulta controlada por datos que permite asignar datos a consultas parametrizadas.

Copia de objetos de base de datos.

Con DTS, puede transferir, además de los datos, índices, vistas, inicios de sesión, procedimientos almacenados, desencadenadores, reglas, valores predeterminados, restricciones y tipos de datos definidos por el usuario. Además, puede generar secuencias de comandos para copiar los objetos de base de datos.

ANEXO:

CURSORES

Las operaciones de una base de datos relacional actúan en un conjunto completo de filas. El conjunto de filas que devuelve una instrucción SELECT está compuesto de todas las filas que satisfacen las condiciones de la cláusula WHERE de la instrucción. Este conjunto completo de filas que devuelve la instrucción se conoce como el conjunto de resultados.

Las aplicaciones, especialmente las aplicaciones interactivas en línea, no siempre pueden trabajar de forma efectiva con el conjunto de resultados completo si lo toman como una unidad. Estas aplicaciones necesitan un mecanismo que trabaje con una fila o un pequeño bloque de filas cada vez. Los cursores son una extensión de los conjuntos de resultados que proporcionan dicho mecanismo.

Los cursores amplían el procesamiento de los resultados porque:

• Permiten situarse en filas específicas del conjunto de resultados.
• Recuperan una fila o bloque de filas de la posición actual en el conjunto de resultados.
• Aceptan modificaciones de los datos de las filas en la posición actual del conjunto de resultados
• Aceptan diferentes grados de visibilidad para los cambios que realizan otros usuarios en la información de la base de datos que se presenta en el conjunto de resultados.
• Proporcionan instrucciones de Transact-SQL en secuencias de comandos, procedimientos almacenados y acceso de desencadenadores a los datos de un conjunto de resultados.

Proceso de cursores

1. Asigne un cursor al conjunto de resultados de una instrucción Transact-SQL y defina las características del cursor como, por ejemplo, si sus filas se pueden actualizar.
2. Ejecute la instrucción de Transact-SQL para llenar el cursor.
3. Recupere las filas del cursor que desea ver. La operación de recuperar una fila o un bloque de filas de un cursor recibe el nombre de recopilación. Realizar series de recopilaciones para recuperar filas, ya sea hacia adelante o hacia atrás, recibe el nombre de desplazamiento.
4. Existe la opción de realizar operaciones de modificación (actualización o eliminación) en la fila de la posición actual del cursor.
5. Cierre el cursor.

Ejemplo:

-- Declare the variables to store the values returned by FETCH.

DECLARE @au_lname varchar(40), @au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR

SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE "B%"

ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- Concatenate and display the current values in the variables.

PRINT "Author: " + @au_fname + " " + @au_lname

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

GO